package com.deng.maintenance.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.deng.common.SQLHelper;
import com.deng.maintenance.dao.IKPIValidate;
import com.deng.maintenance.vo.KpiValueVo;
import com.deng.util.DataFormater;
import com.deng.util.DateTools;
import com.deng.util.MsgReader;

public class KPIValidateImpl extends SQLHelper implements IKPIValidate {


	
	
	private int isOverDue(int y,int m)
	{
		int date = new Integer(new MsgReader().getPt("OVERDUE_DATE"));
		if(date == 0)
		{
			return 0;
		}
		Calendar ca1 = Calendar.getInstance();
		ca1.clear();
		ca1.set(y, m-1, date);
		
		Calendar caNow = Calendar.getInstance();
		if(caNow.before(ca1))
		{
			return 0;
		}
		else
		{
			return 1;
		}
		
	}
	
	public List<KpiValueVo> getKpiPointByFilter(int guikou_id,int kaohe_id,
							int item_id,String item_name,int status,int db,int y,int m){
		List<KpiValueVo> rtList = new ArrayList<KpiValueVo>();
		try{
			Connection conn = getConnect();
//			String replaceGuikou = getSqlGuikouId(guikou_ids);//由于用户可能同事具有多个科室的权限，因此guikou_ids也是多选的。
//			String sql = "select m.kpi_id,m.item_name,m.display_name,m.point,m.target_value," +
//					"	m.description,m.frequence,m.target_type,m.standard_type,m.detail_type," +
//					"	m.different_scale,m.add_type,m.scale_point,m.formula,m.default_value," +
//					"	m.section_detail,m.danwei," +
//					"	m.kaohe_id,(select name from dbo.t_sys_stat_department where t_sys_stat_department.id = m.kaohe_id) as kaohe_name," +
//					"	m.guikou_id,(select name from dbo.t_sys_stat_department where t_sys_stat_department.id = m.guikou_id) as guikou_name," +
//					"	m.c_id,m.c_item_name,m.c_display_name,m.c_target_value,m.c_default_value," +
//					"	m.c_target_type,m.c_different_scale,m.c_add_type,m.c_scale_point," +
//					"	m.c_formula,m.c_no_double,m.c_danwei,m.kpi_detail_id," +
//					"	kv.id as v_id,kdv.id as vc_id," +
//					"   kv.value," +y+" as year,"+m+" as month,"+
//					"	kv.create_user,kv.create_time,kv.score,kv.update_score,kv.detail,kv.valid_detail," +
//					"	kv.update_user,kv.update_time,kv.update_description,kv.valid,kv.valid_user,kv.valid_time," +
//					"	kdv.value as c_value,kdv.score as c_score," +
//					"	kdv.create_user as c_create_user,kdv.create_time as c_create_time,kdv.detail as c_detail," +
//					"	kdv.update_score as c_update_score,kdv.update_user as c_update_user,kdv.update_time as c_update_time," +
//					"	kdv.valid as c_valid,kdv.valid_user as c_valid_user,kdv.valid_time as c_valid_time" +
//					
//					"   from" +
//					"   (" +
//					"	select "+y+" as year,"+m+" as month,kpi_id,item_name,display_name,point,target_value,description,frequence,target_type," +
//					"	standard_type,detail_type,different_scale,add_type,scale_point,formula,default_value," +
//					"	section_detail,danwei,kaohe_id," +
//					"	null as c_id,null as c_item_name,null as c_display_name,null as c_target_value,null as c_default_value,null as c_target_type," +
//					"	null as c_different_scale,null as c_add_type,null as c_scale_point,null as c_formula,null as c_no_double,null as c_danwei," +
//					"	1 as kpi_detail_id," +
//					"	guikou_id from KPI_VS_ORGANIZATION " +
//					"	where kpi_id = " +kpi_id+" and guikou_id != -1 " +
//					(replaceGuikou.equals("0")?"":"	and guikou_id in (" +replaceGuikou+")")+
//					"	union all" +
//					"	select "+y+" as year,"+m+" as month," +
//					"	a.kpi_id,a.item_name,a.display_name,a.point,a.target_value,a.description,a.frequence,a.target_type," +
//					"	a.standard_type,a.detail_type,a.different_scale,a.add_type,a.scale_point,a.formula,a.default_value," +
//					"	a.section_detail,a.danwei,a.kaohe_id," +
//					"	b.kpi_detail_id as c_id,b.item_name as c_item_name,b.display_name as c_display_name," +
//					"	b.target_value as c_target_value,b.default_value as c_default_value,b.target_type as c_target_type," +
//					"	b.different_scale as c_different_scale,b.add_type as c_add_type,b.scale_point as c_scale_point," +
//					"	b.formula as c_formula,b.no_double as c_no_double,b.danwei as c_danwei," +
//					"	1 as kpi_detail_id,b.guikou_id from (" +
//					"	select KPI_VS_ORGANIZATION.*,KPI_DETAIL_VS_ORGANIZATION.kpi_detail_id from KPI_VS_ORGANIZATION,KPI_DETAIL_VS_ORGANIZATION" +
//					"	where KPI_VS_ORGANIZATION.kaohe_id = KPI_DETAIL_VS_ORGANIZATION.kaohe_id" +
//					"	and KPI_VS_ORGANIZATION.guikou_id = -1" +
//					"	and KPI_VS_ORGANIZATION.kpi_id = KPI_DETAIL_VS_ORGANIZATION.parent_id" +
//					(replaceGuikou.equals("0")?"":"	and KPI_DETAIL_VS_ORGANIZATION.guikou_id in (" +replaceGuikou+")")+
//					"	) as a" +
//					"	left join KPI_DETAIL_VS_ORGANIZATION as b" +
//					"	on a.kpi_id = b.parent_id" +
//					"	and a.kaohe_id = b.kaohe_id" +
//					"	and a.kpi_detail_id = b.kpi_detail_id" +
//					"	where a.kpi_id = " +kpi_id+					
//					
//					"   ) as m" +
//					"   left join KPI_VALUE as kv" +
//					"   on" +
//					"   m.kpi_id = kv.kpi_id" +
//					"   and kv.year = m.year and kv.month = m.month" +
////					"   and kv.guikou_id = m.guikou_id" +
//					"	and kv.kaohe_id = m.kaohe_id " +
//					"   left join KPI_DETAIL_VALUE as kdv" +
//					"   on" +
//					"   kdv.year = m.year and kdv.month = m.month" +
//					"   and kdv.kpi_detail_id = m.c_id" +
//					"   and kdv.guikou_id = m.guikou_id" +
//					"   and kdv.kaohe_id = m.kaohe_id" +
//					"   order by kaohe_name";
//				
//			ResultSet rs = queryData(conn,sql);
//			List<KpiValueVo> rtList = new ArrayList();
//			KpiValueVo vo ;
//			while(rs.next()){
//				vo = new KpiValueVo();
//				vo.is_over_due = isOverDue;
//				
//				vo.is_my_detail = rs.getInt("kpi_detail_id");//一个父下面可以有几个明细，但是可能并非所有明细都是本父科室的。
//				
//				vo.kpi_id	=	rs.getInt("kpi_id");
//				vo.kpi_name	=	rs.getString("item_name");
//				vo.display_name	=	rs.getString("display_name");
//				vo.point	=	DataFormater.numberToString(rs.getBigDecimal("point"));
//				vo.target_value	=	DataFormater.numberToString(rs.getBigDecimal("target_value"));
//				vo.description	=	rs.getString("description");
//				vo.frequence	=	rs.getString("frequence");
//				vo.target_type	=	rs.getInt("target_type");
//				vo.standard_type	=	rs.getInt("standard_type");
//				vo.detail_type	=	rs.getInt("detail_type");
//				vo.different_scale	=	DataFormater.numberToString(rs.getBigDecimal("different_scale"));
//				vo.add_type	=	rs.getInt("add_type");
//				vo.scale_point	=	DataFormater.numberToString(rs.getBigDecimal("scale_point"));
//				vo.formula	=	rs.getString("formula");
//				vo.default_value	=	rs.getString("default_value");
//				vo.section_detail	=	rs.getString("section_detail");
//				vo.danwei	=	rs.getInt("danwei");
//				vo.kaohe_id	=	rs.getInt("kaohe_id");
//				vo.kaohe_name	=	rs.getString("kaohe_name");
//				vo.guikou_id	=	rs.getInt("guikou_id");
//				vo.guikou_name	=	rs.getString("guikou_name");
//				vo.detail = rs.getString("detail");
//				
//				vo.value	=	DataFormater.numberToString(rs.getBigDecimal("value"));
//				if(vo.value != null && !vo.value.equals(""))
//				{
//					vo.tmp_value = vo.value;
//				}
//				else
//				{
//					vo.tmp_value = vo.default_value;
//				}
//				
//				vo.create_user	=	rs.getString("create_user");
//				vo.create_time	=	rs.getDate("create_time");
//				
//				vo.score	=	rs.getString("score");				
//				vo.update_score	=	DataFormater.numberToString(rs.getBigDecimal("update_score"));
//				vo.tmp_update_score	=	vo.update_score;
//				
//				vo.update_user	=	rs.getString("update_user");
//				vo.update_time	=	rs.getDate("update_time");
//				vo.update_description	=	rs.getString("update_description");
//				vo.valid	=	rs.getInt("valid");
//				vo.tmp_valid	=	vo.valid;
//				vo.valid_user	=	rs.getString("valid_user");
//				vo.valid_time	=	rs.getDate("valid_time");
//				vo.valid_detail  = rs.getString("valid_detail");
//				
//				vo.v_id = rs.getInt("v_id");
//				vo.vc_id = rs.getInt("vc_id");
//				vo.c_id	= rs.getInt("c_id");
//				vo.c_item_name	=	rs.getString("c_item_name");
//				vo.c_display_name	=	rs.getString("c_display_name");
//				vo.c_target_value	=	DataFormater.numberToString(rs.getBigDecimal("c_target_value"));
//				vo.c_default_value	=	DataFormater.numberToString(rs.getBigDecimal("c_default_value"));
//				vo.c_target_type	=	rs.getInt("c_target_type");
//				vo.c_different_scale	=	DataFormater.numberToString(rs.getBigDecimal("c_different_scale"));
//				vo.c_add_type	=	rs.getInt("c_add_type");
//				vo.c_scale_point	=	DataFormater.numberToString(rs.getBigDecimal("c_scale_point"));
//				vo.c_formula	=	rs.getString("c_formula");
//				vo.c_no_double	=	rs.getInt("c_no_double");
//				vo.c_danwei	=	rs.getInt("c_danwei");
////				vo.id	=	rs.getInt("id");
//				vo.year	=	rs.getInt("year");
//				vo.month	=	rs.getInt("month");				
//				vo.c_id	=	rs.getInt("c_id");
//				vo.c_value	=	DataFormater.numberToString(rs.getBigDecimal("c_value"));
//				if(vo.c_value != null && !vo.c_value.equals(""))
//				{
//					vo.c_tmp_value = vo.c_value;
//				}
//				else
//				{
//					vo.c_tmp_value = vo.c_target_value;
//				}
//				vo.c_score	=	DataFormater.numberToString(rs.getBigDecimal("c_score"));
//				vo.c_create_user	=	rs.getString("c_create_user");
//				vo.c_create_time	=	rs.getDate("c_create_time");
//				vo.c_update_score	=	DataFormater.numberToString(rs.getBigDecimal("c_update_score"));
//				vo.c_tmp_update_score	=	vo.c_update_score;
//				vo.c_update_user	=	rs.getString("c_update_user");
//				vo.c_update_time	=	rs.getDate("c_update_time");
//				vo.c_valid	=	rs.getInt("c_valid");
//				vo.c_valid_user	=	rs.getString("c_valid_user");
//				vo.c_valid_time	=	rs.getDate("c_valid_time");
//				vo.c_detail = rs.getString("c_detail");
//				rtList.add(vo);
//			}
			rtList = getKpiPointByFilter(guikou_id,kaohe_id,item_id,item_name,status,db,y,m,conn);
//			rtList = calParentScore(rtList,conn);
			rtList = assembleParentNode(rtList);
			freeConnection(conn);
			return rtList;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return rtList;
	}

	
	/**
	 * 
	 * 是否达标!
		明细
			越大越好
				c_value<c_target
			越小越好
				c_value>c_target
		
		非明细
			越大越好
				value<target
			越小越好
				value>target
			
			
		父扣分(只有明细):
			标准
			
			单项(不封底)
		
			一票(不封底)
	**/
	public List<KpiValueVo> getKpiPointByFilter(int guikou_id,int kaohe_id,
			int item_id,String item_name,int status,int db,int y,int m,Connection conn){
		List<KpiValueVo> rtList = new ArrayList<KpiValueVo>();
		try{
			if(conn == null){
				conn = getConnect();
			}
			int isOverDue = isOverDue(y,m);
			String ksStr = "";
			if(guikou_id != -1){
				ksStr = " (ko.guikou_id = "+guikou_id+" or kdo.guikou_id = "+guikou_id+")";
			}
			if(kaohe_id != -1){
				if(ksStr.length() == 0){
					ksStr = " (ko.kaohe_id = "+kaohe_id+" or kdo.kaohe_id = "+kaohe_id+")";
				}else{
					ksStr += " and (ko.kaohe_id = "+kaohe_id+" or kdo.kaohe_id = "+kaohe_id+")";
				}
				
			}
			if(item_id != -1){
				if(ksStr.length() == 0){
					ksStr = " (ko.kpi_id = "+item_id+")";
				}else{
					ksStr += " and (ko.kpi_id = "+item_id+")";
				}				
			}else if(item_name != null && item_name.trim().length() > 0){
				if(ksStr.length() == 0){
					ksStr = " (ko.item_name like '%"+item_name+"%' or ko.display_name  like '%"+item_name+"%' )";
				}else{
					ksStr += " and (ko.item_name like '%"+item_name+"%' or ko.display_name  like '%"+item_name+"%' )";
				}	
			}

//			if(m == 3 || m == 9){
//				if(ksStr.length() == 0){
//					ksStr = " ko.frequence in('0004','0003')";
//				}else{
//					ksStr += " and ko.frequence in('0004','0003')";
//				}
//			}else if(m == 6){
//				if(ksStr.length() == 0){
//					ksStr = " ko.frequence in('0004','0003','0002')";
//				}else{
//					ksStr += " and ko.frequence in('0004','0003','0002')";
//				}
//			}else if(m == 12){
////				if(ksStr.length() == 0){
////					ksStr = " ko.frequence in('0004','0003','0002','0001')";
////				}else{
////					ksStr += " and ko.frequence in('0004','0003','0002','0001')";
////				}
//			}else{
//				if(ksStr.length() == 0){
//					ksStr = " ko.frequence = '0004'";
//				}else{
//					ksStr += " and ko.frequence = '0004'";
//				}
//			}
//			<mx:Object label="未打分" value="0"/>
//			<mx:Object label="主任未审核" value="5"/>
//			<mx:Object label="科主任已审核" value="4"/>
//			<mx:Object label="管考办已审核" value="1"/>
//			<mx:Object label="管考办发回重填" value="2"/>
//			<mx:Object label="重新填报完成" value="3"/>			
//			<mx:Object label="--全部--" value="-1"/>
			if(status == -1){
				//全体都显示
			}else if(status == 0){//未打分  意味着为valid实际为NULL
				if(ksStr.length() == 0){
					ksStr = "(kv.valid is null and kdv.valid is null)" ;
				}else{
					ksStr += " and (kv.valid is null and kdv.valid is null)";
				}
			}else{
				if(ksStr.length() == 0){
					ksStr = " (kv.valid = "+status+" or kdv.valid = "+status+" )";
				}else{
					ksStr += " and (kv.valid = "+status+" or kdv.valid = "+status+" )";
				}
			}
			
			
			if(ksStr.length() > 0){
				ksStr = " where "+ ksStr;
			}
			String sql = "select "+y+" as year,"+m+" as month," +
					"	ko.kaohe_id,duty1.name+'-'+p_kh.name as kaohe_name," +
					"	case when ko.guikou_id != -1 then ko.guikou_id " +
					"		else kdo.guikou_id end as guikou_id," +
					" 	case when ko.guikou_id != -1 then duty2.name+'-'+p_gk.name" +
					"		else duty4.name+'-'+c_gk.name end as guikou_name," +
					//"	ko.guikou_id as guikou_id,duty2.name+'-'+p_gk.name as guikou_name," +
					"	kdo.kaohe_id as c_kaohe_id,duty3.name+'-'+c_kh.name as c_kaohe_name," +
					"	kdo.guikou_id as c_guikou_id,duty4.name+'-'+c_gk.name as c_guikou_name," +
					"	(select sum(p.value) from kpi_detail_value as p  where p.year = "+y+" and p.month = " +m+
					"	and p.kaohe_id = kdo.kaohe_id and p.parent_id = kdo.parent_id) as sum_value,"+
					"	(select sum(p.score) from kpi_detail_value as p  where p.year = "+y+" and p.month = " +m+
					"	and p.kaohe_id = kdo.kaohe_id and p.parent_id = kdo.parent_id) as sum_score," +
//					case when (ko.point+(select sum(p.score) from kpi_detail_value as p  where p.year = @y and p.month = @m 
//					and p.kaohe_id = kdo.kaohe_id and p.parent_id = kdo.parent_id)) > 0  then
//					(ko.point+(select sum(p.score) from kpi_detail_value as p  where p.year = @y and p.month = @m 
//					and p.kaohe_id = kdo.kaohe_id and p.parent_id = kdo.parent_id)) 
//					else 0 end as sum_score,
					"	case " +
					"	when ko.detail_type = 2 and kdo.target_type = 1 and kdv.value < kdo.target_value then 2" +
					"	when ko.detail_type = 2 and kdo.target_type = 2 and kdv.value > kdo.target_value then 2" +
					"	when ko.detail_type = 2 and kdo.target_type = 0 and kdv.value is not null then 2" +
					"	when ko.detail_type = 3 and kdv.value is not null then 2" +
					"	when ko.detail_type != 2 and ko.target_type = 1 and kv.value < ko.target_value then 2" +
					"	when ko.detail_type != 2 and ko.target_type = 2 and kv.value > ko.target_value then 2" +
					"	when ko.detail_type != 2 and ko.target_type = 0 and kv.value is not null then 2" +
					"	else 1 end as dabiao,"+
					
					"	ko.kpi_id as kpi_id,ko.item_name as item_name,ko.display_name as display_name," +
					"	ko.description as description,ko.point as point,ko.target_value as target_value," +
					"	ko.frequence as frequence,ko.target_type as target_type,ko.standard_type as standard_type," +
					"	ko.detail_type as detail_type,ko.different_scale as different_scale,ko.add_type as add_type," +
					"	ko.scale_point as scale_point,ko.formula as formula,ko.default_value as default_value," +
					"	ko.section_detail as section_detail,ko.danwei as danwei," +
					
					"	kdo.kpi_detail_id as c_id,kdo.item_name as c_item_name,kdo.display_name as c_display_name," +
					"	kdo.target_value as c_target_value,kdo.default_value as c_default_value," +
					"	kdo.target_type as c_target_type,kdo.different_scale as c_different_scale,kdo.add_type as c_add_type," +
					"	kdo.scale_point as c_scale_point,kdo.formula as c_formula,kdo.no_double as c_no_double," +
					"	kdo.danwei as c_danwei," +
					//"	c_kh.name as c_kaohe_name," +
					
					"	kv.id as v_id,kv.value as value,kv.create_user as create_user,kv.create_time as create_time," +
					"	kv.score as score,kv.update_score as update_score,kv.update_user as update_user," +
					"	kv.update_time as update_time,kv.update_description as update_description," +
					"	kv.valid as valid,kv.valid_user as valid_user,kv.valid_time as valid_time," +
					"	kv.detail as detail,kv.valid_detail as valid_detail," +

					"	kdv.id as vc_id,kdv.value as c_value," +
					"	kdv.score as c_score,kdv.create_user as c_create_user,kdv.create_time as c_create_time," +
					"	kdv.update_score as c_update_score,kdv.update_user as c_update_user,kdv.update_time as c_update_time," +
					"	kdv.valid as c_valid,kdv.valid_user as c_valid_user,kdv.valid_time as c_valid_time," +
					"	kdv.update_description as c_update_description," +
					"	kdv.detail as c_detail,kdv.valid_detail as c_valid_detail" +
					
					"	from KPI_VS_ORGANIZATION as ko" +
					"	left join KPI_DETAIL_VS_ORGANIZATION as kdo" +
					"	on  ko.kpi_id = kdo.parent_id" +
					"	and ko.kaohe_id = kdo.kaohe_id" +
					"	left join KPI_VALUE as kv" +
					"	on kv.year = "+y+" and kv.month = " +m+
					"	and kv.kpi_id = ko.kpi_id and kv.kaohe_id = ko.kaohe_id and kv.guikou_id = ko.guikou_id" +
					"	left join KPI_DETAIL_VALUE as kdv" +
					"	on kdv.year = "+y+" and kdv.month = " +m+
					"	and kdv.kaohe_id = kdo.kaohe_id and kdv.guikou_id = kdo.guikou_id and kdv.kpi_detail_id = kdo.kpi_detail_id" +
					"	left join t_sys_stat_department as p_kh" +
					"	on ko.kaohe_id = p_kh.id" +
					" 	left join dict_duty_type as duty1" +
					"	on duty1.id = p_kh.duty_id" +
					"	left join t_sys_stat_department as p_gk" +
					"	on ko.guikou_id = p_gk.id" +
					"	left join dict_duty_type as duty2" +
					"	on duty2.id = p_gk.duty_id" +
					"	left join t_sys_stat_department as c_kh" +
					"	on kdo.kaohe_id = c_kh.id" +
					" 	left join dict_duty_type as duty3" +
					"	on duty3.id = c_kh.duty_id" +
					"	left join t_sys_stat_department as c_gk" +
					"	on kdo.guikou_id = c_gk.id" +
					"	left join dict_duty_type as duty4" +
					"	on duty4.id = c_gk.duty_id" +
					ksStr;
			
			
			if(db == 2){//不达标
//				sql = "SELECT * FROM ("+sql+") as X ";
				sql = "SELECT * FROM ("+sql+") as X " +
				"	WHERE dabiao = 2" +
				"	order by guikou_id,kpi_id,kaohe_id,c_id";
			}else if(db == 1){//达标 =1
				sql = "SELECT * FROM ("+sql+") as X " +
				"	WHERE dabiao = 1" +
				"	order by guikou_id,kpi_id,kaohe_id,c_id";
			}else{
				sql +="	order by guikou_id,kpi_id,kaohe_id,c_id";
			}
			ResultSet rs = queryData(conn, sql);
			KpiValueVo vo;
			while(rs.next()){
				vo = new KpiValueVo();
				vo.is_over_due = isOverDue;
				
//				vo.is_my_detail = rs.getInt("kpi_detail_id");//一个父下面可以有几个明细，但是可能并非所有明细都是本父科室的。
				
				vo.kpi_id	=	rs.getInt("kpi_id");
				vo.kpi_name	=	rs.getString("item_name");
				
				vo.kaohe_id	=	rs.getInt("kaohe_id");
				vo.kaohe_name	=	rs.getString("kaohe_name");
				vo.guikou_id	=	rs.getInt("guikou_id");
				vo.guikou_name	=	rs.getString("guikou_name");
				
				vo.c_kaohe_id	=	rs.getInt("c_kaohe_id");
				vo.c_kaohe_name	=	rs.getString("c_kaohe_name");
				vo.c_guikou_id	=	rs.getInt("c_guikou_id");
				vo.c_guikou_name	=	rs.getString("c_guikou_name");
				
				
				vo.display_name	=	rs.getString("display_name");
				vo.point	=	DataFormater.numberToString(rs.getBigDecimal("point"));
				vo.target_value	=	DataFormater.numberToString(rs.getBigDecimal("target_value"));
				vo.description	=	rs.getString("description");
				vo.frequence	=	rs.getString("frequence");
				vo.target_type	=	rs.getInt("target_type");
				vo.standard_type	=	rs.getInt("standard_type");
				vo.detail_type	=	rs.getInt("detail_type");
				vo.different_scale	=	DataFormater.numberToString(rs.getBigDecimal("different_scale"));
				vo.add_type	=	rs.getInt("add_type");
				vo.scale_point	=	DataFormater.numberToString(rs.getBigDecimal("scale_point"));
				vo.formula	=	rs.getString("formula");
				vo.default_value	=	rs.getString("default_value");
				vo.section_detail	=	rs.getString("section_detail");
				vo.danwei	=	rs.getInt("danwei");				
				vo.detail = rs.getString("detail");
				
				
				if(vo.detail_type == 2){//明细
					vo.value = DataFormater.numberToString(rs.getBigDecimal("sum_value"));
//					if(vo.standard_type == 1){//单项奖惩 允许突破下限
//						vo.score =	new BigDecimal(vo.point).add(new BigDecimal(rs.getString("sum_score"))).toString();
//					}else{
//						if(new BigDecimal(vo.point).add(new BigDecimal(rs.getString("sum_score"))).compareTo(new BigDecimal(0)) < 0){
//							vo.score =	"0";
//						}else{
//							vo.score =	new BigDecimal(vo.point).add(new BigDecimal(rs.getString("sum_score"))).toString();
//						}
//					}
					vo.score = rs.getString("sum_score");
					vo.valid_detail  = rs.getString("c_valid_detail");
					vo.tmp_valid	=	rs.getInt("c_valid");
				}else{
					vo.value = DataFormater.numberToString(rs.getBigDecimal("value"));
					vo.score	=	rs.getString("score");	
					vo.valid_detail  = rs.getString("valid_detail");
					vo.tmp_valid	=	rs.getInt("valid");
				}
				
				
				if(vo.value != null && !vo.value.equals(""))
				{
					vo.tmp_value = vo.value;
				}
				else
				{
					vo.tmp_value = vo.default_value;
				}
				
				vo.create_user	=	rs.getString("create_user");
				vo.create_time	=	rs.getDate("create_time");
				
							
				vo.update_score	=	DataFormater.numberToString(rs.getBigDecimal("update_score"));
				vo.tmp_update_score	=	vo.update_score;
				
				vo.update_user	=	rs.getString("update_user");
				vo.update_time	=	rs.getDate("update_time");
				vo.update_description	=	rs.getString("update_description");
				vo.valid	=	rs.getInt("valid");
				vo.valid_user	=	rs.getString("valid_user");
				vo.valid_time	=	rs.getDate("valid_time");
				
				
				vo.v_id = rs.getInt("v_id");
				vo.vc_id = rs.getInt("vc_id");
				vo.c_id	= rs.getInt("c_id");
				vo.c_item_name	=	rs.getString("c_item_name");
				vo.c_display_name	=	rs.getString("c_display_name");
				vo.c_target_value	=	DataFormater.numberToString(rs.getBigDecimal("c_target_value"));
				vo.c_default_value	=	DataFormater.numberToString(rs.getBigDecimal("c_default_value"));
				vo.c_target_type	=	rs.getInt("c_target_type");
				vo.c_different_scale	=	DataFormater.numberToString(rs.getBigDecimal("c_different_scale"));
				vo.c_add_type	=	rs.getInt("c_add_type");
				vo.c_scale_point	=	DataFormater.numberToString(rs.getBigDecimal("c_scale_point"));
				vo.c_formula	=	rs.getString("c_formula");
				vo.c_no_double	=	rs.getInt("c_no_double");
				vo.c_danwei	=	rs.getInt("c_danwei");
				vo.year	=	rs.getInt("year");
				vo.month	=	rs.getInt("month");	
				vo.c_value	=	DataFormater.numberToString(rs.getBigDecimal("c_value"));
				if(vo.c_value != null && !vo.c_value.equals(""))
				{
					vo.c_tmp_value = vo.c_value;
				}
				else
				{
					vo.c_tmp_value = vo.c_target_value;
				}
				vo.c_score	=	DataFormater.numberToString(rs.getBigDecimal("c_score"));
				vo.c_create_user	=	rs.getString("c_create_user");
				vo.c_create_time	=	rs.getDate("c_create_time");
				vo.c_update_score	=	DataFormater.numberToString(rs.getBigDecimal("c_update_score"));
				vo.c_tmp_update_score	=	vo.c_update_score;
				vo.c_update_user	=	rs.getString("c_update_user");
				vo.c_update_time	=	rs.getDate("c_update_time");
				vo.c_valid	=	rs.getInt("c_valid");
				vo.c_valid_user	=	rs.getString("c_valid_user");
				vo.c_valid_time	=	rs.getDate("c_valid_time");
				vo.c_detail = rs.getString("c_detail");
				
				vo.dabiao = rs.getInt("dabiao");
				rtList.add(vo);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return rtList;
	}
	
	private List<KpiValueVo> assembleParentNode(List<KpiValueVo> list){
		try{
			int kpi_id = 0;	
			int kaohe_id = 0;
			for(int i = 0;i<list.size();i++)//接着增加明细项目的父项目
			{	
				KpiValueVo ovo = list.get(i);
				ovo.uid = ovo.guikou_id+"."+ovo.kaohe_id+"."+ovo.kpi_id+"."+ovo.c_id;
				//itemVo.guikou_id+"-"+itemVo.kaohe_id+"-"+itemVo.kpi_id+"-"+itemVo.c_id
				if(ovo.detail_type == 2)//明细
				{
					if(kpi_id == ovo.kpi_id && kaohe_id == ovo.kaohe_id){
						continue;
					}
					kpi_id = ovo.kpi_id;
					kaohe_id = ovo.kaohe_id;
					KpiValueVo vo = new KpiValueVo();
					vo.kpi_id	=	ovo.kpi_id;
					vo.kpi_name	=	ovo.kpi_name;
					vo.display_name	=	ovo.display_name;
					vo.point	=	ovo.point;
					
					
					
					vo.target_value	=	ovo.target_value;
					vo.description	=	ovo.description;
					vo.frequence	=	ovo.frequence;
					vo.target_type	=	ovo.target_type;
					vo.standard_type	=	ovo.standard_type;
					vo.detail_type	=	ovo.detail_type;
					vo.different_scale	=	ovo.different_scale;
					vo.add_type	=	ovo.add_type;
					vo.scale_point	=	ovo.scale_point;
					vo.formula	=	ovo.formula;
					vo.default_value	=	ovo.default_value;
					vo.section_detail	=	ovo.section_detail;
					vo.danwei	=	ovo.danwei;
					vo.kaohe_id	=	ovo.kaohe_id;
					vo.kaohe_name	=	ovo.kaohe_name;					
					vo.guikou_name	=	ovo.guikou_name;
					
					vo.update_score = ovo.update_score;
					vo.tmp_update_score = vo.update_score;
					
					vo.year = ovo.year;
					vo.month = ovo.month;
					
					
//					int x = 0;有几个子就变成几
//					for(KpiValueVo tmpVo:list)
//					{
//						if(tmpVo.year == vo.year
//							&& tmpVo.month == vo.month
//							&& tmpVo.kpi_id == vo.kpi_id
//							&& tmpVo.kaohe_id == vo.kaohe_id
//							&& tmpVo.c_id != 0
//							)
//						{
//							x ++;
//						}
//					}
					vo.v_id = ovo.v_id;//
//					vo.v_id = x;
					
					vo.detail = ovo.detail;
					vo.valid = ovo.valid;
					vo.tmp_valid = ovo.valid;				
					vo.valid_detail = ovo.valid_detail;
					
					
					
					vo.guikou_id	=	ovo.guikou_id;
					vo.score = assembleParentScore(ovo);
					vo.uid = "-1."+vo.kaohe_id+"."+vo.kpi_id+".0";
					list.add(i, vo);
				}			
			}
			return list;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return list;
	}
	private String assembleParentScore(KpiValueVo vo){
		if(vo.standard_type == 0){//最低0分
//			System.out.println(vo.point+"  "+vo.score);
			if(vo.score == null){
				return "0";
			}
			if(new BigDecimal(vo.point).add(new BigDecimal(vo.score)).compareTo(new BigDecimal(0))<0){
				return "0";
			}else{
				return new BigDecimal(vo.point).add(new BigDecimal(vo.score)).toString();
			}
		}else{
			return vo.score;
		}
	}
//	public int validate(String user_code,KpiValueVo vo){
//		try{
//			Connection conn = getConnect();
//			String detail = "";
//			if(vo.valid_detail.length() > 0){
//				detail = "'"+vo.valid_detail+"'";
//			}else{
//				detail = "NULL";
//			}
//			String sql = "";
//			if(vo.detail_type == 2){//明细的子
//				sql = "update KPI_DETAIL_VALUE " +
//					"set " +
//					"valid = 1"+
//					",valid_user = '"+user_code+"'"+
//					",valid_time = '"+DateTools.dtsWithTime(new Date())+"'"+
//					",valid_detail = "+detail+""+
//					" where id = " +vo.vc_id;				
//			}else{
//				sql = "update KPI_VALUE " +
//					"set " +
//					"valid = 1"+
//					",valid_user = '"+user_code+"'"+
//					",valid_time = '"+DateTools.dtsWithTime(new Date())+"'"+
//					",valid_detail = "+detail+""+
//					" where id = " +vo.v_id;	
//			}
//			return updateData(conn, sql);
//		}catch(Exception ex){
//			ex.printStackTrace();
//		}
//		return 0;
//	}
//	public int deny(String user_code,KpiValueVo vo){
//		try{
//			String detail = "";
//			if(vo.valid_detail.length() > 0){
//				detail = "'"+vo.valid_detail+"'";
//			}else{
//				detail = "NULL";
//			}
//			Connection conn = getConnect();
//			String sql = "";
//			if(vo.detail_type == 2){//明细的子
//				sql = "update KPI_DETAIL_VALUE " +
//					"set " +
//					"valid = 2"+
//					",valid_user = '"+user_code+"'"+
//					",valid_time = '"+DateTools.dtsWithTime(new Date())+"'"+
//					",valid_detail = "+detail+
//					" where id = " +vo.vc_id;				
//			}else{
//				sql = "update KPI_VALUE " +
//					"set " +
//					"valid = 2"+
//					",valid_user = '"+user_code+"'"+
//					",valid_time = '"+DateTools.dtsWithTime(new Date())+"'"+
//					",valid_detail = "+detail+
//					" where id = " +vo.v_id;	
//			}
//			return updateData(conn, sql);
//		}catch(Exception ex){
//			ex.printStackTrace();
//		}
//		return 0;
//	}
	
//	<mx:Object label="未打分" value="0"/>
//	<mx:Object label="主任未审核" value="5"/>
//	<mx:Object label="科主任已审核" value="4"/>
//	<mx:Object label="管考办已审核" value="1"/>
//	<mx:Object label="管考办发回重填" value="2"/>
//	<mx:Object label="重新填报完成" value="3"/>	-----不需要这个状态了.凡是发回重新填写,一旦填写,状态就变成5.然后在4,再审核		
//	<mx:Object label="--全部--" value="-1"/>
	public String validThisPage(List<KpiValueVo> l,String user_id){
		Connection conn = getConnect();	
		try
		{			
			conn.setAutoCommit(false);
			String sql ;
			for(KpiValueVo vo :l)
			{
				if(vo.tmp_valid != 0){
					if(vo.c_id !=0 ){
						System.out.println("明细:"+vo.uid);
						if(vo.c_tmp_update_score.trim() == "")//没有临时数据，不需要修改tmp_update_score
						{
							sql = "update KPI_DETAIL_VALUE " +
								"set " +
								"valid = "+vo.tmp_valid+
								",valid_user = '"+user_id+"'"+
								",valid_time = '"+DateTools.dtsWithTime(new Date())+"'"+
								",valid_detail = "+sqlForEmpty(vo.valid_detail)+""+
								" where id = " +vo.vc_id+
								" and valid = 4";				
						}
						else
						{
							sql = "update KPI_DETAIL_VALUE " +
								"set " +
								"valid = "+vo.tmp_valid+
								",valid_user = '"+user_id+"'"+
								",valid_time = '"+DateTools.dtsWithTime(new Date())+"'"+
								",valid_detail = "+sqlForEmpty(vo.valid_detail)+						
								",update_score= "+sqlForEmpty(vo.c_tmp_update_score)+
								",update_user= " +sqlForEmpty(user_id) +
								",update_time ='"+DateTools.dtsWithTime(new Date())+"'"+
								" where id = " +vo.vc_id+
								" and valid = 4";		
						}
					}else{
						System.out.println("非明细"+vo.uid);
						if(vo.tmp_update_score.trim() == "")//没有临时数据，不需要修改tmp_update_score
						{
							sql = "update KPI_VALUE " +
								"set " +
								"valid = "+vo.tmp_valid+
								",valid_user = '"+user_id+"'"+
								",valid_time = '"+DateTools.dtsWithTime(new Date())+"'"+
								",valid_detail = "+sqlForEmpty(vo.valid_detail)+""+
								" where id = " +vo.v_id+
								" and valid = 4";							
						}
						else
						{
							sql = "update KPI_VALUE " +
								"set " +
								"valid = "+vo.tmp_valid+
								",valid_user = '"+user_id+"'"+
								",valid_time = '"+DateTools.dtsWithTime(new Date())+"'"+
								",valid_detail = "+sqlForEmpty(vo.valid_detail)+						
								",update_score= "+sqlForEmpty(vo.tmp_update_score)+
								",update_user= " +sqlForEmpty(user_id) +
								",update_time ='"+DateTools.dtsWithTime(new Date())+"'"+
								" where id = " +vo.v_id+
								" and valid = 4";		
						}
					}
					updateData(conn, sql);
				}else{
					System.out.println("不变"+vo.uid);
				}
			}
			conn.commit();
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
			try{
				if(conn != null){
					conn.rollback();//回滚。
				}
			}catch(Exception ex2){
				ex2.printStackTrace();
			}
		}finally{
			try{
				if(conn != null){
					conn.setAutoCommit(true);
				}
			}catch(Exception ex2){
				ex2.printStackTrace();
			}
		}
		return "SUCCESS";
	}

	
	public String validThisPageByPass(List<KpiValueVo> l,String user_id){
		Connection conn = getConnect();	
		try
		{
			conn.setAutoCommit(false);
			String sql ;
			for(KpiValueVo vo :l)
			{
				if(vo.detail_type == 2){//明细
					if(vo.c_id == 0){//明细父(父的c_id=0)
						System.out.println("明细父(不操作):"+vo.uid);
						continue;
					}else if(vo.vc_id != 0 && (vo.c_valid == 3 || vo.c_valid == 4)){
						System.out.println("明细子(符合操作条件):"+vo.uid);
						if(vo.c_tmp_update_score.trim() == "")//没有临时数据，不需要修改tmp_update_score
						{
							sql = "update KPI_DETAIL_VALUE " +
								"set " +
								"valid = 1"+
								",valid_user = '"+user_id+"'"+
								",valid_time = '"+DateTools.dtsWithTime(new Date())+"'"+
								",valid_detail = "+sqlForEmpty(vo.valid_detail)+""+
								" where id = " +vo.vc_id+
								" and valid = 4";			
						}
						else
						{
							sql = "update KPI_DETAIL_VALUE " +
								"set " +
								"valid = 1"+
								",valid_user = '"+user_id+"'"+
								",valid_time = '"+DateTools.dtsWithTime(new Date())+"'"+
								",valid_detail = "+sqlForEmpty(vo.valid_detail)+						
								",update_score= "+sqlForEmpty(vo.c_tmp_update_score)+
								",update_user= " +sqlForEmpty(user_id) +
								",update_time ='"+DateTools.dtsWithTime(new Date())+"'"+
								" where id = " +vo.vc_id+
								" and valid = 4";		
						}
					}else{
						System.out.println("明细子(不操作):"+vo.uid);
						continue;
					}
				}else{
					if(vo.valid == 3 || vo.valid == 4){
						System.out.println("非明细(符合操作条件)"+vo.uid);
						if(vo.tmp_update_score.trim() == "")//没有临时数据，不需要修改tmp_update_score
						{
							sql = "update KPI_VALUE " +
								"set " +
								"valid = 1"+
								",valid_user = '"+user_id+"'"+
								",valid_time = '"+DateTools.dtsWithTime(new Date())+"'"+
								",valid_detail = "+sqlForEmpty(vo.valid_detail)+""+
								" where id = " +vo.v_id+
								" and valid = 4";							
						}
						else
						{
							sql = "update KPI_VALUE " +
								"set " +
								"valid = 1"+
								",valid_user = '"+user_id+"'"+
								",valid_time = '"+DateTools.dtsWithTime(new Date())+"'"+
								",valid_detail = "+sqlForEmpty(vo.valid_detail)+						
								",update_score= "+sqlForEmpty(vo.tmp_update_score)+
								",update_user= " +sqlForEmpty(user_id) +
								",update_time ='"+DateTools.dtsWithTime(new Date())+"'"+
								" where id = " +vo.v_id+
								" and valid = 4";		
						}
					}else{
						System.out.println("非明细(不操作)"+vo.uid);
						continue;
					}					
				}
				updateData(conn, sql);
			}
			conn.commit();
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
			try{
				if(conn != null){
					conn.rollback();//回滚。
				}
			}catch(Exception ex2){
				ex2.printStackTrace();
			}
		}finally{
			try{
				if(conn != null){
					conn.setAutoCommit(true);
				}
			}catch(Exception ex2){
				ex2.printStackTrace();
			}
		}
		return "SUCCESS";
	}
	
	private String sqlForEmpty(String s){
		if(s == null || "".equals(s.trim())){
			return " NULL ";
		}else{
			return "'"+s+"'";
		}
	}
	
	
	
	
	
}
